Project 1: Supply Chain Sustainability#
Packages and Settings#
import pandas as pd
import warnings
import plotly.express as px
import numpy as np
pd.set_option('display.max_colwidth', 0)
pd.set_option('display.max_columns', None)
pd.options.display.max_seq_items = 2000
warnings.filterwarnings('ignore')
%%html
<style>
.dataframe td {
white-space: nowrap;
}
</style>
Initial Datasets Import#
Import Shipped Order Lines#
df_lines = pd.read_csv('../data/order_lines.csv', index_col = 0)
print("{:,} order lines to process".format(len(df_lines)))
df_lines.head()
5,208 order lines to process
| Date | Month-Year | Warehouse Code | Customer Code | Order Number | Order Line Number | Item Code | Units | Euros | |
|---|---|---|---|---|---|---|---|---|---|
| 112 | 2021-01-04 00:00:00.000 | 1-2021 | 3403434 | 5002915-15 | 20247100 | 1 | 963543-43 | 2.0 | 381.47 |
| 14485 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 1 | 8902753-53 | 250.0 | 187.03 |
| 14486 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 2 | 8308591-91 | 500.0 | 452.45 |
| 14487 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 3 | 8308621-21 | 500.0 | 452.45 |
| 14488 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 4 | 8022921-21 | 400.0 | 567.16 |
Import Master Data: Unit of Measure Conversions to (kg)#
df_uom = pd.read_csv('../data/uom_conversions.csv', index_col = 0)
print("{:,} Unit of Measure Conversions".format(len(df_uom)))
# Join
df_join = df_lines.copy()
COLS_JOIN = ['Item Code']
df_join = pd.merge(df_join, df_uom, on=COLS_JOIN, how='left', suffixes=('', '_y'))
df_join.drop(df_join.filter(regex='_y$').columns.tolist(),axis=1, inplace=True)
print("{:,} records".format(len(df_join)))
df_join.head()
557 Unit of Measure Conversions
5,208 records
| Date | Month-Year | Warehouse Code | Customer Code | Order Number | Order Line Number | Item Code | Units | Euros | Conversion Ratio | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-04 00:00:00.000 | 1-2021 | 3403434 | 5002915-15 | 20247100 | 1 | 963543-43 | 2.0 | 381.47 | 56.58400 |
| 1 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 1 | 8902753-53 | 250.0 | 187.03 | 0.03200 |
| 2 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 2 | 8308591-91 | 500.0 | 452.45 | 0.02219 |
| 3 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 3 | 8308621-21 | 500.0 | 452.45 | 0.02219 |
| 4 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 4 | 8022921-21 | 400.0 | 567.16 | 0.03200 |
Import Distances#
df_dist = pd.read_csv('../data/' + 'distances.csv', index_col = 0)
# Location
df_dist['Location'] = df_dist['Customer Country'].astype(str) + ', ' + df_dist['Customer City'].astype(str)
df_dist.head()
| Warehouse Code | Warehouse Name | Warehouse Country | Warehouse City | Customer Code | Customer Country | Customer City | Road | Rail | Sea | Air | Location | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 19 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5002915-15 | FRANCE | LES ANGLES | 765.728 | 0.0 | 0.0 | 0.0 | FRANCE, LES ANGLES |
| 610 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5397843-43 | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE |
| 676 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340669-69 | GERMANY | PEINE - WOLTORF | 856.000 | 0.0 | 0.0 | 0.0 | GERMANY, PEINE - WOLTORF |
| 682 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340672-72 | FRANCE | BELLEVILLE | 52.694 | 0.0 | 0.0 | 0.0 | FRANCE, BELLEVILLE |
| 1375 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5029913-13 | FRANCE | MOISSY-CRAMAYEL | 288.437 | 0.0 | 0.0 | 0.0 | FRANCE, MOISSY-CRAMAYEL |
Import Cities GPS Locations#
df_gps = pd.read_csv('../data/' + 'gps_locations.csv', index_col = 0)
print("{:,} Locations".format(len(df_gps)))
df_gps.head()
19 Locations
| Location | GPS 1 | GPS 2 | |
|---|---|---|---|
| 867 | BULGARIA, DOLNI BOGROV | 42.701462 | 23.490811 |
| 1087 | FRANCE, AUBROMETZ | 50.303749 | 2.176058 |
| 1153 | FRANCE, BELLEVILLE | 48.871184 | 2.386682 |
| 1371 | FRANCE, CLICHY CEDEX | 48.904099 | 2.304123 |
| 1534 | FRANCE, GAEL | 48.116682 | -2.234640 |
Data Processing#
Merge Distance with GPS Locations#
df_dist = pd.merge(df_dist, df_gps, on='Location', how='left', suffixes=('', '_y'))
df_dist.drop(df_dist.filter(regex='_y$').columns.tolist(),axis=1, inplace=True)
df_dist
| Warehouse Code | Warehouse Name | Warehouse Country | Warehouse City | Customer Code | Customer Country | Customer City | Road | Rail | Sea | Air | Location | GPS 1 | GPS 2 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5002915-15 | FRANCE | LES ANGLES | 765.728 | 0.0 | 0.0 | 0.0 | FRANCE, LES ANGLES | 42.579678 | 2.048950 |
| 1 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5397843-43 | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 |
| 2 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340669-69 | GERMANY | PEINE - WOLTORF | 856.000 | 0.0 | 0.0 | 0.0 | GERMANY, PEINE - WOLTORF | 52.304594 | 10.312521 |
| 3 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340672-72 | FRANCE | BELLEVILLE | 52.694 | 0.0 | 0.0 | 0.0 | FRANCE, BELLEVILLE | 48.871184 | 2.386682 |
| 4 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5029913-13 | FRANCE | MOISSY-CRAMAYEL | 288.437 | 0.0 | 0.0 | 0.0 | FRANCE, MOISSY-CRAMAYEL | 48.624666 | 2.595756 |
| 5 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5003451-51 | FRANCE | LEVALLOIS PERRET | 295.165 | 0.0 | 0.0 | 0.0 | FRANCE, LEVALLOIS PERRET | 48.894640 | 2.287419 |
| 6 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5293132-32 | FRANCE | HERLIN LE SEC | 353.590 | 0.0 | 0.0 | 0.0 | FRANCE, HERLIN LE SEC | 50.354429 | 2.331766 |
| 7 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5003269-69 | FRANCE | MACON | 478.723 | 0.0 | 0.0 | 0.0 | FRANCE, MACON | 48.878552 | 2.280117 |
| 8 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5003474-74 | FRANCE | ORLY | 287.110 | 0.0 | 0.0 | 0.0 | FRANCE, ORLY | 48.740227 | 2.402965 |
| 9 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5003476-76 | FRANCE | VITRY SUR SEINE | 284.851 | 0.0 | 0.0 | 0.0 | FRANCE, VITRY SUR SEINE | 48.789263 | 2.395103 |
| 10 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 115-15 | FRANCE | CLICHY CEDEX | 293.858 | 0.0 | 0.0 | 0.0 | FRANCE, CLICHY CEDEX | 48.904099 | 2.304123 |
| 11 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5006517-17 | FRANCE | SERRIS | 256.250 | 0.0 | 0.0 | 0.0 | FRANCE, SERRIS | 48.845309 | 2.787302 |
| 12 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5335348-48 | UNITED KINGDOM | BRISTOL | 626.000 | 0.0 | 44.0 | 0.0 | UNITED KINGDOM, BRISTOL | 51.468489 | -2.590717 |
| 13 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 9001746-46 | FRANCE | AUBROMETZ | 357.151 | 0.0 | 0.0 | 0.0 | FRANCE, AUBROMETZ | 50.303749 | 2.176058 |
| 14 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 9066250-50 | FRANCE | GAEL | 667.613 | 0.0 | 0.0 | 0.0 | FRANCE, GAEL | 48.116682 | -2.234640 |
| 15 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5334855-55 | UNITED KINGDOM | KENT | 409.000 | 0.0 | 44.0 | 0.0 | UNITED KINGDOM, KENT | 51.196396 | 0.742644 |
| 16 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5494889-89 | FRANCE | GRETZ ARMAINVILLIERS | 268.336 | 0.0 | 0.0 | 0.0 | FRANCE, GRETZ ARMAINVILLIERS | 48.753586 | 2.727228 |
| 17 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5262267-67 | MAURITANIA | NOUAKCHOTT | 300.000 | 0.0 | 3739.0 | 0.0 | MAURITANIA, NOUAKCHOTT | 18.067171 | -15.953561 |
| 18 | 3402002 | WAREHOUSE PARIS AREA 2 | FRANCE | MONTMIRAIL | 5486697-97 | BULGARIA | DOLNI BOGROV | 2163.000 | 0.0 | 0.0 | 0.0 | BULGARIA, DOLNI BOGROV | 42.701462 | 23.490811 |
Final Join to build records#
COLS_JOIN = ['Warehouse Code', 'Customer Code']
df_join = pd.merge(df_join, df_dist, on = COLS_JOIN, how='left', suffixes=('', '_y'))
df_join.drop(df_join.filter(regex='_y$').columns.tolist(),axis=1, inplace=True)
print("{:,} records".format(len(df_join)))
df_join
5,208 records
| Date | Month-Year | Warehouse Code | Customer Code | Order Number | Order Line Number | Item Code | Units | Euros | Conversion Ratio | Warehouse Name | Warehouse Country | Warehouse City | Customer Country | Customer City | Road | Rail | Sea | Air | Location | GPS 1 | GPS 2 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-04 00:00:00.000 | 1-2021 | 3403434 | 5002915-15 | 20247100 | 1 | 963543-43 | 2.0 | 381.47 | 56.58400 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | LES ANGLES | 765.728 | 0.0 | 0.0 | 0.0 | FRANCE, LES ANGLES | 42.579678 | 2.048950 |
| 1 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 1 | 8902753-53 | 250.0 | 187.03 | 0.03200 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 |
| 2 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 2 | 8308591-91 | 500.0 | 452.45 | 0.02219 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 |
| 3 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 3 | 8308621-21 | 500.0 | 452.45 | 0.02219 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 |
| 4 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 4 | 8022921-21 | 400.0 | 567.16 | 0.03200 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 5203 | 2021-10-28 00:00:00.000 | 10-2021 | 3403434 | 5397843-43 | 21089497 | 84 | 802065-65 | 80.0 | 72.96 | 0.02200 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 |
| 5204 | 2021-10-28 00:00:00.000 | 10-2021 | 3403434 | 5397843-43 | 21089497 | 85 | 942082-82 | 200.0 | 391.88 | 0.03200 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 |
| 5205 | 2021-10-28 00:00:00.000 | 10-2021 | 3403434 | 5397843-43 | 21089497 | 86 | 8022922-22 | 400.0 | 581.40 | 0.03200 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 |
| 5206 | 2021-11-01 00:00:00.000 | 10-2021 | 3403434 | 5340669-69 | 21004473 | 1 | 982884-84 | 2000.0 | 2980.00 | 0.14600 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | GERMANY | PEINE - WOLTORF | 856.000 | 0.0 | 0.0 | 0.0 | GERMANY, PEINE - WOLTORF | 52.304594 | 10.312521 |
| 5207 | 2021-11-01 00:00:00.000 | 10-2021 | 3403434 | 5340669-69 | 21004473 | 2 | 983347-47 | 2400.0 | 4848.00 | 0.16950 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | GERMANY | PEINE - WOLTORF | 856.000 | 0.0 | 0.0 | 0.0 | GERMANY, PEINE - WOLTORF | 52.304594 | 10.312521 |
5208 rows × 22 columns
Calculation at order line level#
# Calculation @ line level
df_line = df_join.copy()
df_line['KG'] = df_line['Units'] * df_line['Conversion Ratio']
dict_co2e = dict(zip(['Air' ,'Sea', 'Road', 'Rail'], [2.1, 0.01, 0.096, 0.028]))
MODES = ['Road', 'Rail','Sea', 'Air']
for mode in MODES:
df_line['CO2 ' + mode] = df_line['KG'].astype(float)/1000 * df_line[mode].astype(float) * dict_co2e[mode]
df_line['CO2 Total'] = df_line[['CO2 ' + mode for mode in MODES]].sum(axis = 1)
df_line.to_csv('../data/detailed_report.csv')
df_line.head()
| Date | Month-Year | Warehouse Code | Customer Code | Order Number | Order Line Number | Item Code | Units | Euros | Conversion Ratio | Warehouse Name | Warehouse Country | Warehouse City | Customer Country | Customer City | Road | Rail | Sea | Air | Location | GPS 1 | GPS 2 | KG | CO2 Road | CO2 Rail | CO2 Sea | CO2 Air | CO2 Total | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-04 00:00:00.000 | 1-2021 | 3403434 | 5002915-15 | 20247100 | 1 | 963543-43 | 2.0 | 381.47 | 56.58400 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | LES ANGLES | 765.728 | 0.0 | 0.0 | 0.0 | FRANCE, LES ANGLES | 42.579678 | 2.048950 | 113.168 | 8.318967 | 0.0 | 0.0 | 0.0 | 8.318967 |
| 1 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 1 | 8902753-53 | 250.0 | 187.03 | 0.03200 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 | 8.000 | 0.216183 | 0.0 | 0.0 | 0.0 | 0.216183 |
| 2 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 2 | 8308591-91 | 500.0 | 452.45 | 0.02219 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 | 11.095 | 0.299818 | 0.0 | 0.0 | 0.0 | 0.299818 |
| 3 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 3 | 8308621-21 | 500.0 | 452.45 | 0.02219 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 | 11.095 | 0.299818 | 0.0 | 0.0 | 0.0 | 0.299818 |
| 4 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | 5397843-43 | 20258239 | 4 | 8022921-21 | 400.0 | 567.16 | 0.03200 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | FRANCE | SUCY EN BRIE | 281.488 | 0.0 | 0.0 | 0.0 | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 | 12.800 | 0.345892 | 0.0 | 0.0 | 0.0 | 0.345892 |
Calculation at order level#
# Calculate Weight (KG)
df_join['KG'] = df_join['Units'] * df_join['Conversion Ratio']
# Agg by order
GPBY_ORDER = ['Date', 'Month-Year',
'Warehouse Code', 'Warehouse Name', 'Warehouse Country', 'Warehouse City',
'Customer Code', 'Customer Country', 'Customer City','Location', 'GPS 1', 'GPS 2',
'Road', 'Rail', 'Sea', 'Air',
'Order Number']
df_agg = pd.DataFrame(df_join.groupby(GPBY_ORDER)[['Units', 'KG']].sum())
df_agg.reset_index(inplace = True)
df_agg.head()
| Date | Month-Year | Warehouse Code | Warehouse Name | Warehouse Country | Warehouse City | Customer Code | Customer Country | Customer City | Location | GPS 1 | GPS 2 | Road | Rail | Sea | Air | Order Number | Units | KG | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-04 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5002915-15 | FRANCE | LES ANGLES | FRANCE, LES ANGLES | 42.579678 | 2.048950 | 765.728 | 0.0 | 0.0 | 0.0 | 20247100 | 2.0 | 113.16800 |
| 1 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340669-69 | GERMANY | PEINE - WOLTORF | GERMANY, PEINE - WOLTORF | 52.304594 | 10.312521 | 856.000 | 0.0 | 0.0 | 0.0 | 20203388 | 153.0 | 2530.32471 |
| 2 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340672-72 | FRANCE | BELLEVILLE | FRANCE, BELLEVILLE | 48.871184 | 2.386682 | 52.694 | 0.0 | 0.0 | 0.0 | 20203383 | 128.0 | 2116.87296 |
| 3 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5397843-43 | FRANCE | SUCY EN BRIE | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 | 281.488 | 0.0 | 0.0 | 0.0 | 20258239 | 2771.0 | 71.45232 |
| 4 | 2021-01-08 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5003451-51 | FRANCE | LEVALLOIS PERRET | FRANCE, LEVALLOIS PERRET | 48.894640 | 2.287419 | 295.165 | 0.0 | 0.0 | 0.0 | 21000061 | 2125.0 | 111.95200 |
Calculate CO2 = f(KG, Ratios)#
# CO2 Emissions
dict_co2e = dict(zip(['Air' ,'Sea', 'Road', 'Rail'], [2.1, 0.01, 0.096, 0.028]))
MODES = ['Road', 'Rail','Sea', 'Air']
for mode in MODES:
df_agg['CO2 ' + mode] = df_agg['KG'].astype(float)/1000 * df_agg[mode].astype(float) * dict_co2e[mode]
df_agg['CO2 Total'] = df_agg[['CO2 ' + mode for mode in MODES]].sum(axis = 1)
df_agg.head()
| Date | Month-Year | Warehouse Code | Warehouse Name | Warehouse Country | Warehouse City | Customer Code | Customer Country | Customer City | Location | GPS 1 | GPS 2 | Road | Rail | Sea | Air | Order Number | Units | KG | CO2 Road | CO2 Rail | CO2 Sea | CO2 Air | CO2 Total | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-04 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5002915-15 | FRANCE | LES ANGLES | FRANCE, LES ANGLES | 42.579678 | 2.048950 | 765.728 | 0.0 | 0.0 | 0.0 | 20247100 | 2.0 | 113.16800 | 8.318967 | 0.0 | 0.0 | 0.0 | 8.318967 |
| 1 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340669-69 | GERMANY | PEINE - WOLTORF | GERMANY, PEINE - WOLTORF | 52.304594 | 10.312521 | 856.000 | 0.0 | 0.0 | 0.0 | 20203388 | 153.0 | 2530.32471 | 207.931963 | 0.0 | 0.0 | 0.0 | 207.931963 |
| 2 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340672-72 | FRANCE | BELLEVILLE | FRANCE, BELLEVILLE | 48.871184 | 2.386682 | 52.694 | 0.0 | 0.0 | 0.0 | 20203383 | 128.0 | 2116.87296 | 10.708464 | 0.0 | 0.0 | 0.0 | 10.708464 |
| 3 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5397843-43 | FRANCE | SUCY EN BRIE | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 | 281.488 | 0.0 | 0.0 | 0.0 | 20258239 | 2771.0 | 71.45232 | 1.930845 | 0.0 | 0.0 | 0.0 | 1.930845 |
| 4 | 2021-01-08 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5003451-51 | FRANCE | LEVALLOIS PERRET | FRANCE, LEVALLOIS PERRET | 48.894640 | 2.287419 | 295.165 | 0.0 | 0.0 | 0.0 | 21000061 | 2125.0 | 111.95200 | 3.172254 | 0.0 | 0.0 | 0.0 | 3.172254 |
Final mapping for visualization#
# Mapping the delivery Mode
df_agg['Delivery Mode'] = df_agg[MODES].astype(float).apply(
lambda t: [mode if t[mode]>0 else '-' for mode in MODES], axis = 1)
dict_map = dict(zip(df_agg['Delivery Mode'].astype(str).unique(),
[i.replace(", '-'",'').replace("'-'",'').replace("'",'') for i in df_agg['Delivery Mode'].astype(str).unique()]))
df_agg['Delivery Mode'] = df_agg['Delivery Mode'].astype(str).map(dict_map)
df_agg
| Date | Month-Year | Warehouse Code | Warehouse Name | Warehouse Country | Warehouse City | Customer Code | Customer Country | Customer City | Location | GPS 1 | GPS 2 | Road | Rail | Sea | Air | Order Number | Units | KG | CO2 Road | CO2 Rail | CO2 Sea | CO2 Air | CO2 Total | Delivery Mode | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-04 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5002915-15 | FRANCE | LES ANGLES | FRANCE, LES ANGLES | 42.579678 | 2.048950 | 765.728 | 0.0 | 0.0 | 0.0 | 20247100 | 2.0 | 113.16800 | 8.318967 | 0.0 | 0.000000 | 0.0 | 8.318967 | [Road] |
| 1 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340669-69 | GERMANY | PEINE - WOLTORF | GERMANY, PEINE - WOLTORF | 52.304594 | 10.312521 | 856.000 | 0.0 | 0.0 | 0.0 | 20203388 | 153.0 | 2530.32471 | 207.931963 | 0.0 | 0.000000 | 0.0 | 207.931963 | [Road] |
| 2 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340672-72 | FRANCE | BELLEVILLE | FRANCE, BELLEVILLE | 48.871184 | 2.386682 | 52.694 | 0.0 | 0.0 | 0.0 | 20203383 | 128.0 | 2116.87296 | 10.708464 | 0.0 | 0.000000 | 0.0 | 10.708464 | [Road] |
| 3 | 2021-01-07 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5397843-43 | FRANCE | SUCY EN BRIE | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 | 281.488 | 0.0 | 0.0 | 0.0 | 20258239 | 2771.0 | 71.45232 | 1.930845 | 0.0 | 0.000000 | 0.0 | 1.930845 | [Road] |
| 4 | 2021-01-08 00:00:00.000 | 1-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5003451-51 | FRANCE | LEVALLOIS PERRET | FRANCE, LEVALLOIS PERRET | 48.894640 | 2.287419 | 295.165 | 0.0 | 0.0 | 0.0 | 21000061 | 2125.0 | 111.95200 | 3.172254 | 0.0 | 0.000000 | 0.0 | 3.172254 | [Road] |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 286 | 2021-10-26 00:00:00.000 | 10-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340672-72 | FRANCE | BELLEVILLE | FRANCE, BELLEVILLE | 48.871184 | 2.386682 | 52.694 | 0.0 | 0.0 | 0.0 | 21004398 | 9040.0 | 416.74880 | 2.108175 | 0.0 | 0.000000 | 0.0 | 2.108175 | [Road] |
| 287 | 2021-10-27 00:00:00.000 | 10-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5029913-13 | FRANCE | MOISSY-CRAMAYEL | FRANCE, MOISSY-CRAMAYEL | 48.624666 | 2.595756 | 288.437 | 0.0 | 0.0 | 0.0 | 21089663 | 13515.0 | 493.52420 | 13.665661 | 0.0 | 0.000000 | 0.0 | 13.665661 | [Road] |
| 288 | 2021-10-27 00:00:00.000 | 10-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5334855-55 | UNITED KINGDOM | KENT | UNITED KINGDOM, KENT | 51.196396 | 0.742644 | 409.000 | 0.0 | 44.0 | 0.0 | 21009276 | 450.0 | 35.34930 | 1.387955 | 0.0 | 0.015554 | 0.0 | 1.403509 | [Road, Sea] |
| 289 | 2021-10-28 00:00:00.000 | 10-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5397843-43 | FRANCE | SUCY EN BRIE | FRANCE, SUCY EN BRIE | 48.768810 | 2.537369 | 281.488 | 0.0 | 0.0 | 0.0 | 21089497 | 11831.0 | 367.71380 | 9.936674 | 0.0 | 0.000000 | 0.0 | 9.936674 | [Road] |
| 290 | 2021-11-01 00:00:00.000 | 10-2021 | 3403434 | WAREHOUSE PARIS AREA 1 | FRANCE | CHALONS-EN-CHAMPAGNE | 5340669-69 | GERMANY | PEINE - WOLTORF | GERMANY, PEINE - WOLTORF | 52.304594 | 10.312521 | 856.000 | 0.0 | 0.0 | 0.0 | 21004473 | 4400.0 | 698.80000 | 57.424589 | 0.0 | 0.000000 | 0.0 | 57.424589 | [Road] |
291 rows × 25 columns
# Save Final Report
df_agg.to_csv('../data/final_report.csv')
Visualisation#
df = pd.read_csv("../data/final_report.csv")
fig = px.scatter_mapbox(
df,
lat='GPS 1', # Latitude column
lon='GPS 2', # Longitude column
color='Delivery Mode', # Color bubbles by transport mode
size='CO2 Total', # Bubble size by total CO2
hover_name='Location', # What shows up when hovering over a point
hover_data=['CO2 Total'], # Additional info in the hover tooltip
zoom=4, # Map zoom level
height=600 # Figure height in pixels
)
# Use an open-source map style so no token is needed:
fig.update_layout(mapbox_style="carto-positron")
fig.update_layout(title="CO₂ Emissions by Delivery Mode")
fig.show()
df = pd.read_csv("../data/detailed_report.csv")
df_agg = df.groupby(["Customer Country", "Item Code"])["CO2 Total"].sum().reset_index()
fig = px.bar(
df_agg,
x="CO2 Total",
y="Customer Country",
color="Item Code",
orientation="h", # Horizontal bars
hover_data=["Item Code", "CO2 Total"],
title="CO₂ by Customer Country and Item Code"
)
# Make sure the bars stack on top of each other rather than side by side
fig.update_layout(
barmode="stack",
xaxis_title="CO₂ Total",
yaxis_title="Customer Country"
)
fig.show()
df = pd.read_csv("../data/detailed_report.csv")
df_agg = df.groupby("Customer City").agg({"Euros": "sum", "CO2 Total": "sum"}).reset_index()
threshold = 500 # Only label if CO2 > 500
df_agg["Label"] = np.where(df_agg["CO2 Total"] > threshold, df_agg["Customer City"], "")
fig = px.scatter(
df_agg,
x="Euros", # Horizontal axis
y="CO2 Total", # Vertical axis
text="Label", # Show the city names on each data point
#color="Delivery Mode", # optional grouping
size="CO2 Total", # optional sizing by CO₂
hover_data=["Customer City"], # optional extra data in hover tooltip
title="CO₂ = f(Turnover) by City Destination"
)
# Position the text labels nicely (above each point)
fig.update_traces(textposition="top center")
# Optionally format axes (e.g., showing 0.5M or 1.5M for large values):
fig.update_layout(
xaxis=dict(
title="Turnover (Euros)",
tickformat=".1f", # show one decimal, e.g., 0.5M
),
yaxis=dict(
title="CO₂ Total (kg or relevant unit)"
),
template="simple_white"
)
fig.show()